<?php

namespace Superadmin\Model;

use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\Sql\Sql; //join
use Superadmin\Model\Categories;
use Superadmin\Model\Video;
use Zend\Db\Sql\Select;

class VideoTable extends AbstractTableGateway {

    protected $table = "tblvideo";
    public $sql;

    public function __construct(Adapter $adapter) {
        $this->adapter = $adapter;
        $this->resultSetPrototype = new ResultSet();
        $this->resultSetPrototype->setArrayObjectPrototype(new Categories());
        $this->initialize();
        $this->sql = new Sql($this->adapter);
    }

    public function loadVideo($start, $rowper, $key, $cateId, $sortbycol, $sortby) {
        $batdau = 0;
        $batdau = (($start - 1) * $rowper);
        $sqlex = $this->sql->select();
        $sqlex->from($this->table);
        $sqlex->columns(array(
            'videoId' => 'videoId',
            'videoTitle' => 'videoTitle',
            'videoDescription' => 'videoDescription',
            'videodate' => 'videodate',
            'cateId' => 'cateId',
        ));
        $sqlex->join("tblcategories", 'tblvideo.cateId=tblcategories.cateId', array('cateName' => 'cateName'));
        //$sqlex->join("tblcategories", 'tblvideo.cateId=tblcategories.cateId');
        if ($key != null) {
            $sqlex->where->like('videoTitle', '%' . $key . '%');
        }
        if ($cateId != null) {
            $sqlex->where(array('tblvideo.cateId' => $cateId));
        }
        $sqlex->order('' . $sortbycol . ' ' . $sortby . '');
        $sqlex->limit($rowper);
        $sqlex->offset($batdau);

        $stament = $this->sql->prepareStatementForSqlObject($sqlex);
        $results = $stament->execute();
        $array = array();
        foreach ($results as $result) {
            $tmp = array();
            $tmp = $result;
            $array[] = $tmp;
        }

        return $array;
    }

    public function countLoadVideo($key, $cateId) {
        $sqlex = $this->sql->select();
        $sqlex->from($this->table);
        $sqlex->columns(array(
            'videoId' => 'videoId',
            'videoTitle' => 'videoTitle',
            'videoDescription' => 'videoDescription',
            'videodate' => 'videodate',
            'cateId' => 'cateId',
        ));
        $sqlex->join("tblcategories", 'tblvideo.cateId=tblcategories.cateId', array('cateName' => 'cateName',));
        if ($key != null) {
            $sqlex->where->like('tblvideo.videoTitle', '%' . $key . '%');
        }
        if ($cateId != null) {
            $sqlex->where(array('tblvideo.cateId' => $cateId));
        }
        $sqlex->order('videoId DESC');
        $stament = $this->sql->prepareStatementForSqlObject($sqlex);
        $results = $stament->execute();
        $array = array();
        foreach ($results as $result) {
            $tmp = array();
            $tmp = $result;
            $array[] = $tmp;
        }
        return count($array);
    }

    public function delVideos($videoId) {
        $this->delImg($videoId);
        $sqlEx = $this->sql->delete();
        $sqlEx->from($this->table);
        $sqlEx->where(array('videoId' => $videoId));
        $stament = $this->sql->prepareStatementForSqlObject($sqlEx);
        $results = $stament->execute();
        if ($results != null) {
            return true;
        } else {
            return false;
        }
    }

    public function addNew(Video $objvideo) {
        $data = array();
        $data["videoId"] = $objvideo->videoId;
        $data["videoTitle"] = $objvideo->videoTitle;
        $data["videoDescription"] = $objvideo->videoDescription;
        $data["videoIframe"] = $objvideo->videoIframe;
        $data["videoimg"] = $objvideo->videoimg;
        $data["videodate"] = $objvideo->videodate;
        $data["cateId"] = $objvideo->cateId;
        $sqlEx = $this->sql->insert();
        $sqlEx->into($this->table);
        $sqlEx->values($data);
        $stament = $this->sql->prepareStatementForSqlObject($sqlEx);
        $results = $stament->execute();
        if ($results != null) {
            return true;
        } else {
            return false;
        }
    }

    public function getdataedit($videoId) {
        $sqlex = $this->sql->select();
        $sqlex->from($this->table);

        $sqlex->where(array('videoId' => $videoId));

        $stament = $this->sql->prepareStatementForSqlObject($sqlex);
        $results = $stament->execute();
        $data = $results->current();

        return $data;
    }

    public function delImg($videoId) {
        $data = $this->getdataedit($videoId);
        $urlImg = $data["videoimg"];
        $appp = APPLICATION_PATH . URL_UPLOAD;
        $file = $appp . "/" . $urlImg;
        unlink($file);
    }

    public function updateVideo($videoId,Video $objVideo) {
        $data = array();
       
        $data["videoTitle"] = $objVideo->videoTitle;
        $data["videoIframe"] = $objVideo->videoIframe;
        $data["videoimg"] = $objVideo->videoimg;
        $data["videoDescription"] = $objVideo->videoDescription;
        $data["cateId"] = (int)$objVideo->cateId;
        $sqlEx=$this->sql->update($this->table);
       
        $sqlEx->set($data);
        $sqlEx->where($videoId);
        $pst=$this->sql->prepareStatementForSqlObject($sqlEx);
        $result=$pst->execute();
        if($result !=null){
            return true;
        }else{
            return false;
        }
    }

}
